# -*- coding: utf-8 -*-
from flask import Flask, request, jsonify, render_template, redirect, url_for
from flask_cors import CORS
import pandas as pd
import os
import sqlite3
from setting.config import ConfigManager
from database.database import DatabaseManager
import logging

app = Flask(__name__)
CORS(app)

# 修改数据库连接路径（第11行）
DB_PATH = os.path.join(os.path.dirname(__file__), 'database/students.db')
# 配置文件路径
CONFIG_FILE_PATH = os.path.join(os.path.dirname(__file__), 'setting/config.yaml')
# 生成配置对象
config = ConfigManager(CONFIG_FILE_PATH)
# 生成数据库对象
db = DatabaseManager(DB_PATH)


# 确保 database 文件夹存在
if not os.path.exists('database'):
    os.makedirs('database')

# 清空数据库
@app.route('/clear_database', methods=['POST'])
def clear_database():
    try:        
        db.reset()
        return jsonify({'success': True, 'message': '数据库已清空并重新初始化'})
    except Exception as e:
        return jsonify({'success': False, 'message': f'清空数据库失败: {str(e)}'}), 500

# 导入成绩
# 增加一个已导入文件的集合
imported_files = set()

@app.route('/import', methods=['POST'])
@app.route('/import_scores', methods=['POST'])
def import_scores():
    try:
        if 'file' not in request.files:
            return jsonify({'success': False, 'message': '未选择文件'}), 400
            
        file = request.files['file']
        if not file.filename:
            return jsonify({'success': False, 'message': '无效文件'}), 400

        try:
            # 实际文件处理逻辑
            if file.filename.endswith('.csv'):
                df = pd.read_csv(file, dtype={'student_id': str})
            elif file.filename.endswith(('.xls', '.xlsx')):
                df = pd.read_excel(file, dtype={'student_id': str})
            else:
                return jsonify({'success': False, 'message': '不支持的文件格式'}), 400

            # 数据库写入逻辑
            db.import_scores(df.to_dict(orient='records'))
            return jsonify({'success': True, 'message': '成绩导入成功'})

        except pd.errors.ParserError as e:
            app.logger.error(f'文件解析失败: {str(e)}')
            return jsonify({'success': False, 'message': f'文件解析失败: {str(e)}'}), 400
        except sqlite3.IntegrityError as e:
            app.logger.error(f'数据库唯一性约束冲突: {str(e)}')
            return jsonify({'success': False, 'message': '存在重复的学生成绩记录'}), 400
        except Exception as e:
            app.logger.error(f'数据库写入失败: {str(e)}')
            return jsonify({'success': False, 'message': f'数据库写入失败: {str(e)}'}), 500

    except Exception as e:
        app.logger.error(f'文件处理异常: {str(e)}')
        return jsonify({'success': False, 'message': f'文件处理失败: {str(e)}'}), 500

    if 'file' not in request.files:
        return jsonify({'success': False, 'message': '未选择文件'}), 400

    file = request.files['file']
    if not file.filename:
        return jsonify({'success': False, 'message': '无效文件'}), 400

    try:
        # 查询数据库中是否已存在该文件名（不含扩展名）
        
        file_name_without_ext = os.path.splitext(file.filename)[0]
        with db as cursor:
            cursor.execute('SELECT COUNT(*) FROM scores WHERE exam_id = ?', (file_name_without_ext,))
            result = cursor.fetchone()[0]

        if result > 0:
            return jsonify({'success': False, 'message': f'该文件 {file_name_without_ext} 已导入，请勿重复导入'}), 400
        if file.filename.endswith('.csv'):
            df = pd.read_csv(file, dtype={'student_id': str})
        elif file.filename.endswith(('.xls', '.xlsx')):
            df = pd.read_excel(file, dtype={'student_id': str})
        else:
            return jsonify({'success': False, 'message': '不支持的文件格式'}), 400
        # 将文件名添加到已导入文件集合
        imported_files.add(file.filename)

        # 严格列名检查
        expected_columns = ['grade', 'exam_id', 'class_id', 'student_id', 'name', 'biology_score', 'school_rank', 'class_rank']
        if not set(expected_columns).issubset(df.columns):
            missing = set(expected_columns) - set(df.columns)
            return jsonify({'success': False, 'message': f'缺少必要列: {missing}'}), 400

        # 保留原有数据，将数据插入到 scores 表
        with db.conn:
            df[expected_columns].to_sql('scores', db.conn, if_exists='append', index=False)

        # # 更新最后考试记录
        # from setting.config import ConfigManager
        # config = ConfigManager('setting/config.yaml')
        # print(file_name_without_ext)
        config.set('exam_setting.last_exam', file_name_without_ext)

        # 导入成功后自动备份数据库
        db.backup()

        return jsonify({'success': True, 'message': f'成功导入 {len(df)} 条记录'})

    except Exception as e:
        return jsonify({'success': False, 'message': f'导入失败: {str(e)}'}), 500
    
# 添加主页路由
@app.route('/')
def index():
    return render_template('index.html')

# 查询所有学生（JSON 格式）
@app.route('/get_all_students')
def get_all_students():
    # 修改导入成绩路由中的数据库连接路径（第74行）
#     with db as cursor:
#         cursor.execute('''SELECT grade, exam_id, class_id AS class_id, student_id, name, biology_score, school_rank, class_rank 
# FROM scores''')
#         students = cursor.fetchall()
    
#     # 将查询结果转换为字典列表
#     columns = [col[0] for col in cursor.description]
#     student_list = [dict(zip(columns, row)) for row in students]
    student_list = db.get_all_scores()
    
    return jsonify(student_list)

# 获取所有班级
@app.route('/get_choosed_classes', methods=['GET'])
def get_choosed_classes():
    try:
        classes = config.get('choosed_classes', [])
        return jsonify({'classes': classes})
    except Exception as e:
        print(f'获取班级配置错误: {str(e)}')
        return jsonify({'classes': []})

@app.route('/get_classes', methods=['GET'])
def get_all_classes():
    try:
        classes = config.get('choosed_classes', [])
        return jsonify({'success': True, 'classes': classes})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取班级失败: {str(e)}'}), 500

# 保存选中班级
@app.route('/save_choosed_classes', methods=['POST'])
def save_choosed_classes():
    try:
        data = request.get_json()
        config.set('choosed_classes', data['classes'])
        return jsonify({'success': True, 'message': '班级配置保存成功'})
    except Exception as e:
        return jsonify({'success': False, 'message': f'保存失败: {str(e)}'}), 500

# 查询所有成绩
@app.route('/get_all_scores', methods=['GET'])
def get_all_scores():
    scores = db.get_all_scores()
    return jsonify(scores)


# 获取配置信息
@app.route('/config', methods=['GET'])
def get_config():
    try:
        config_data = config.get_all()
        return jsonify({'success': True, 'config': config_data})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取配置失败: {str(e)}'}), 500

# 查询数据库状态
@app.route('/get_backups', methods=['GET'])
def get_backups():
    backup_dir = 'backup'
    if not os.path.exists(backup_dir):
        os.makedirs(backup_dir)
    backups = sorted([f for f in os.listdir(backup_dir) if f.endswith('.db')], reverse=True)
    return jsonify({'backups': backups})

@app.route('/get_all_exams', methods=['GET'])
def get_all_exams():
    try:
        exams = db.get_all_exams()
        return jsonify({'success': True, 'exams': exams})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取考试列表失败: {str(e)}'}), 500

@app.route('/get_all_grades', methods=['GET'])
def get_all_grades():
    try:
        grades = db.get_all_grade()
        return jsonify({'success': True, 'grades': grades})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取年级列表失败: {str(e)}'}), 500

@app.route('/get_classes_list', methods=['GET'])
def get_classes_list():
    try:
        classes = db.get_all_classes()
        return jsonify({'success': True, 'classes': classes})
    except Exception as e:
        return jsonify({'success': False, 'message': f'获取班级列表失败: {str(e)}'}), 500

@app.route('/restore', methods=['POST'])
def restore_database():
    try:
        data = request.get_json()
        backup_file = os.path.join('backup', data['backup_file'])
        db.restore(backup_file)
        return jsonify({'success': True, 'message': '数据库还原成功'})
    except Exception as e:
        return jsonify({'success': False, 'message': f'还原失败: {str(e)}'}), 500

@app.route('/database_status', methods=['GET'])
def database_status():
    try:
        # 调用 DatabaseManager 的 get_database_status 方法
        status = db.get_database_status()
        return jsonify({'status': '正常', 'data': status})
    except Exception as e:
        return jsonify({'status': '异常', 'error': str(e)}), 500

# 配置日志
logging.basicConfig(level=logging.DEBUG)


@app.route('/search', methods=['POST'])
def search_scores():
    data = request.get_json()
    results = db.conditional_query(
        grade=data.get('grade'),
        exam_id=data.get('exam_id'),
        class_id=data.get('class_id'),
        name=data.get('name')
    )
    return jsonify(results)

@app.route('/lottery')
def lottery():
    return render_template('lottery.html')

@app.route('/get_students_by_class')
def get_students_by_class():
    # 获取并验证exam_id配置
    exam_id = config.get('exam_setting.last_exam')
    if not exam_id or not isinstance(exam_id, str):
        logging.error(f'无效的exam_id配置: {exam_id}')
        return jsonify({'error': '考试ID配置错误'}), 500
    
    # 获取并验证class_id参数
    class_id = request.args.get('class_id')
    if not class_id:
        logging.error(f'缺少class_id参数: {request.args}')
        return jsonify({'error': '缺少班级ID参数'}), 400
    
    # 转换并验证class_id格式
    try:
        class_id = str(class_id).strip()
        if not class_id.endswith('班'):
            class_id += '班'
    except Exception as e:
        logging.error(f'class_id参数格式错误: {class_id}')
        return jsonify({'error': '班级ID格式错误'}), 400

    # 验证班级存在性
    existing_classes = db.get_all_classes()
    if class_id not in existing_classes:
        logging.error(f'班级{class_id}不存在于数据库')
        return jsonify({'error': '指定班级不存在'}), 404

    # 添加详细日志记录
    logging.info(f'开始查询 考试ID:[{exam_id}] 班级ID:[{class_id}] 有效班级列表:{existing_classes}')
    
    # 执行数据库查询
    if not class_id or not exam_id:
        logging.warning(f'缺少必要参数 class_id: {class_id}, exam_id: {exam_id}')
        return jsonify({'error': '缺少班级ID或考试ID参数'}), 400
    
    try:
        with db as cursor:
            cursor.execute("""
                SELECT name 
                FROM scores 
                WHERE exam_id = ? AND class_id = ?
            """, (exam_id, class_id))
            students = [row[0] for row in cursor.fetchall()]
    
        logging.debug(f'查询到{len(students)}名学生')
        
        if not students:
            logging.warning(f'未找到班级{class_id}在考试{exam_id}中的学生')
            return jsonify({'error': '该班级没有学生数据'}), 404
            
        return jsonify(students)
    except Exception as e:
        logging.error(f'数据库查询失败 exam_id={exam_id} class_id={class_id}', exc_info=True)
        return jsonify({'error': f'数据库查询失败: {str(e)}'}), 500

if __name__ == '__main__':
    logging.debug('Starting Flask application...')
    app.run(debug=True, host='0.0.0.0')

